<?php

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/12/5
 * Time: 14:47
 */
class MysqliConnect
{
    /**连接对象*/
    protected $link;
    /**查询对象*/
    protected $sQuery;
    /**条件集合*/
    protected $where = array();
    /**sql 的参数*/
    protected $settings = array();
    /**最后一条直行的 sql*/
    protected $lastSql = '';
    /**是否执行成功*/
    protected $success = false;
    /**是否是更新*/
    protected $for_update = false;
    /**表名*/
    protected $table = '';
    /**前缀*/
    protected $prefix = '';
    /**每页多少条记录*/
    protected $paging = 10;
    /**设置limit*/
    protected $limit = 0;
    /**返回记录的游标*/
    protected $offset = 0;
    /**ORDER BY 的排序方式,默认为升序*/
    protected $order_asc = true;
    /**什么操作*/
    protected $type = '';
    /**返回数据型式,所有,还是列,还是单元个*/
    protected $read_type = '';
    /**是否返回一条SQL*/
    protected $return_sql = false;
    /**设置列字段名*/
    protected $col_key = '';
    /**选择的列*/
    protected $cols = array();
    /**INSERT 或者 UPDATE 的列*/
    protected $col_values = array();
    /**ORDER BY 的列*/
    protected $order_by = array();
    /**GROUP BY 的列*/
    protected $group_by = array();
    /**HAVING 条件数组*/
    protected $having = array();
    /**返回的列*/
    protected $returning = array();
    /**flags 列表*/
    protected $flags = array();

    /**
     * MysqliConnect constructor.
     * @param $host
     * @param $user
     * @param $pass
     * @param $db_name
     * @param string $prefix
     * @param int $port
     * @param string $charset
     */
    public function __construct($host, $user, $pass, $db_name, $prefix = '', $port = 3306, $charset = 'utf8')
    {
        $this->settings = array(
            'host'    => $host,
            'port'    => $port,
            'user'    => $user,
            'pass'    => $pass,
            'dbname'  => $db_name,
            'charset' => $charset,
            'prefix'  => $prefix,
        );
        $this->connect();
    }

    /**
     * 连接数据库
     */
    public function connect()
    {
        $this->link = mysqli_connect($this->settings['host'], $this->settings['user'], $this->settings['pass'], $this->settings['dbname'], $this->settings['port']);
//        if (!$this->link) {
//            var_dump(mysqli_connect_errno());
//            var_dump(mysqli_connect_error());
//            throw new Exception('mysql connect is error');
//        }
        if($this->link) {
            $charset = empty($this->settings['charset']) ? 'utf8' : str_replace('-', '', $this->settings['charset']);
            mysqli_set_charset($this->link, $charset);
        }
    }

    /**
     * 关闭数据库
     */
    public function closeConnect()
    {
        mysqli_close($this->link);
        $this->link = null;
    }

    /**
     * 来自哪个表
     * @param $table
     * @param bool $autoPrefix
     * @return self
     */
    public function from($table, $autoPrefix = true)
    {
        if ($autoPrefix) {
            $this->table = $this->settings['prefix'] . $table;
        } else {
            $this->table = $table;
        }
        return $this;
    }

    /**
     * 获取数据,配合setRow,setCol,setOne使用
     * @param null $sql
     * @return null|string
     */
    public function get($sql = null)
    {
        $this->type = 'SELECT';
        return $this->execute($sql);
    }

    public function update($sql = null)
    {
        $this->type = 'UPDATE';
        return $this->execute($sql);
    }

    public function delete($sql = null)
    {
        $this->type = 'DELETE';
        return $this->execute($sql);
    }

    public function insert($sql = null)
    {
        $this->type = 'INSERT';
        return $this->execute($sql);
    }

    public function count($sql)
    {
        $this->type = 'COUNT';
        return $this->execute($sql);
    }

    /**
     * 选择字段
     * @param $cols array|mixed 只支持数组和多个元素
     * @return $this
     */
    public function select($cols)
    {
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->cols[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->cols[] = $col;
            }
        }
        return $this;
    }

    public function setSql($bool)
    {
        $this->return_sql = (bool)$bool;
        return $this;
    }

    /**
     * 增加返回的列
     * @param array $cols
     * @return self
     */
    public function addReturning($cols)
    {
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->returning[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->returning[] = $col;
            }
        }
        return $this;
    }

    /**
     * 直接设置列的值
     * @param $cols string|array 字段名称
     * @param null $value 值
     * @return $this 返回当前对象
     */
    public function set($cols, $value = null)
    {
        if ($cols === null) {
            return $this;
        }
        if ($value === null && is_array($cols)) {
            foreach ($cols as $colKey => $colValue) {
                $this->col_values[$this->parseKey($colKey)] = $this->quoteNamesIn($colValue);
            }
        } else {
            $this->col_values[$this->parseKey($cols)] = $this->quoteNamesIn($value);
        }
        return $this;
    }

    /**
     * 设置排序
     * @param $cols array|mixed 只支持数组和多个元素
     * @return $this
     */
    public function orderBy($cols)
    {
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->order_by[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->order_by[] = $col;
            }
        }
        return $this;
    }

    /**
     * 设置倒序排序
     * @param $cols array|mixed 只支持数组和多个元素
     * @return $this
     */
    public function orderByDESC($cols)
    {
        $this->order_asc = false;
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->order_by[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->order_by[] = $col;
            }
        }
        return $this;
    }

    /**
     * 设置limit
     * @param int $limit
     * @return $this
     */
    public function limit($limit = 1)
    {
        $this->limit = (int)$limit;
        return $this;
    }

    /**
     * 设置分组
     * @param $cols
     * @return $this
     */
    public function groupBy($cols)
    {
        if (is_array($cols)) {
            foreach ($cols as $col) {
                $this->group_by[] = $col;
            }
        } else {
            foreach (func_get_args() as $col) {
                $this->group_by[] = $col;
            }
        }
        return $this;
    }

    /**
     * 设置having
     * @param $col
     * @return $this
     */
    public function having($col)
    {
        if (!empty($col)) {
            $this->having[] = $col;
        }
        return $this;
    }

    /**
     * 设置每页的记录数量
     * @param $page
     * @param int $paging
     * @return $this
     */
    public function page($page, $paging = 0)
    {
        $this->limit  = 0;
        $this->offset = 0;
        if ($paging > 0) {
            $this->paging = $paging;
        }
        $page = (int)$page;
        if ($page > 0) {
            $this->limit  = $this->paging;
            $this->offset = $this->paging * ($page - 1);
        }
        return $this;
    }

    /**
     * 设置返回一行数据
     * @return $this
     */
    public function setRow()
    {
        $this->read_type = 'ROW';
        return $this;
    }

    /**
     *  设置返回一列数据
     * @param string $col_key 键值
     * @return $this
     */
    public function setCol($col_key = '')
    {
        $this->read_type = 'COL';
        $this->col_key = $col_key;
        return $this;
    }

    /**
     * 设置返回一个数据
     * @return $this
     */
    public function setOne()
    {
        $this->read_type = 'ONE';
        return $this;
    }

    /**
     * 拼接and的条件
     * @param $field
     * @param $condition
     * @param null $value
     * @return $this
     */
    public function where($field, $condition, $value = null)
    {
        if (!is_string($field) || empty($field)) {
            return $this;
        }
        if ($value === null) {
            $value     = $condition;
            $condition = '=';
        }
        if (count($this->where) == 0) {
            $this->where[] = $this->joinWhere($field, $condition, $value);;
        } else {
            $this->where[] = 'AND ' . $this->joinWhere($field, $condition, $value);
        }
        return $this;
    }

    /**
     *
     * 设置 SQL_CALC_FOUND_ROWS 标记.
     *
     * @param bool $enable
     * @return self
     */
    public function calcFoundRows($enable = true)
    {
        $this->setFlag('SQL_CALC_FOUND_ROWS', $enable);
        return $this;
    }

    /**
     * 设置 SQL_CACHE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function cache($enable = true)
    {
        $this->setFlag('SQL_CACHE', $enable);
        return $this;
    }

    /**
     * 设置 SQL_NO_CACHE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function noCache($enable = true)
    {
        $this->setFlag('SQL_NO_CACHE', $enable);
        return $this;
    }

    /**
     * 设置 STRAIGHT_JOIN 标记.
     *
     * @param bool $enable
     * @return self
     */
    public function straightJoin($enable = true)
    {
        $this->setFlag('STRAIGHT_JOIN', $enable);
        return $this;
    }

    /**
     * 设置 HIGH_PRIORITY 标记
     *
     * @param bool $enable
     * @return self
     */
    public function highPriority($enable = true)
    {
        $this->setFlag('HIGH_PRIORITY', $enable);
        return $this;
    }

    /**
     * 设置 SQL_SMALL_RESULT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function smallResult($enable = true)
    {
        $this->setFlag('SQL_SMALL_RESULT', $enable);
        return $this;
    }

    /**
     * 设置 SQL_BIG_RESULT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function bigResult($enable = true)
    {
        $this->setFlag('SQL_BIG_RESULT', $enable);
        return $this;
    }

    /**
     * 设置 SQL_BUFFER_RESULT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function bufferResult($enable = true)
    {
        $this->setFlag('SQL_BUFFER_RESULT', $enable);
        return $this;
    }

    /**
     * 设置 FOR UPDATE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function forUpdate($enable = true)
    {
        $this->for_update = (bool)$enable;
        return $this;
    }

    /**
     * 设置 DISTINCT 标记
     *
     * @param bool $enable
     * @return self
     */
    public function distinct($enable = true)
    {
        $this->setFlag('DISTINCT', $enable);
        return $this;
    }

    /**
     * 设置 LOW_PRIORITY 标记
     *
     * @param bool $enable
     * @return self
     */
    public function lowPriority($enable = true)
    {
        $this->setFlag('LOW_PRIORITY', $enable);
        return $this;
    }

    /**
     * 设置 IGNORE 标记
     *
     * @param bool $enable
     * @return self
     */
    public function ignore($enable = true)
    {
        $this->setFlag('IGNORE', $enable);
        return $this;
    }

    /**
     * 设置 QUICK 标记
     *
     * @param bool $enable
     * @return self
     */
    public function quick($enable = true)
    {
        $this->setFlag('QUICK', $enable);
        return $this;
    }

    /**
     * 设置 DELAYED 标记
     *
     * @param bool $enable
     * @return self
     */
    public function delayed($enable = true)
    {
        $this->setFlag('DELAYED', $enable);
        return $this;
    }
    /**
     * 开始事务
     */
    public function beginTrans()
    {
        try {
            return mysqli_begin_transaction($this->link);
        } catch (Exception $e) {
            var_dump(mysqli_connect_errno());
        }
    }

    /**
     * 提交事务
     */
    public function commitTrans()
    {
        return $this->pdo->commit();
    }

    /**
     * 事务回滚
     */
    public function rollBackTrans()
    {
        if ($this->pdo->inTransaction()) {
            return $this->pdo->rollBack();
        }
        return true;
    }
    /*****************************************************************/
    /**
     * 清除所有数据
     */
    protected function resetAll()
    {
        $this->for_update = false;
        $this->cols       = array();
        $this->group_by   = array();
        $this->having     = array();
        $this->paging     = 10;
        $this->where      = array();
        $this->order_by   = array();
        $this->col_values = array();
        $this->limit      = 0;
        $this->offset     = 0;
        $this->table      = '';
        $this->return_sql = false;
        $this->order_asc  = true;
        $this->read_type  = '';
        $this->type       = '';
        $this->sQuery     = null;
    }

    /**
     * 获取SQL并执行
     * @param $sql
     * @return array|bool|int|string
     */
    protected function execute($sql)
    {
        if ($sql === null) {
            $sql = $this->build();
        }
        if ($this->return_sql) {
            $this->resetAll();
            return $sql;
        }
        $type      = $this->type;
        $read_type = $this->read_type;
        $this->resetAll();
        $this->query($sql);
        echo $this->type;
        switch ($type) {
            case 'UPDATE':
            case 'DELETE':
                if ($this->sQuery) {
                    return true;
                }
                return false;
                break;
            case 'INSERT':
                if ($this->sQuery) {
                    $this->free();
                    return mysqli_insert_id($this->link);
                }
                return false;
                break;
            case 'COUNT':
                $rows = mysqli_num_rows($this->sQuery);
                $this->free();
                return $rows;
                break;
            default:
                if (!$this->sQuery) {
                    return $this->sQuery;
                }
                switch ($read_type) {
                    case 'ROW':
                        $item = mysqli_fetch_assoc($this->sQuery);
                        $this->free();
                        return $item;
                        break;
                    case 'COL':
                        $result = array();
                        while ($row = mysqli_fetch_assoc($this->sQuery)) {
                            if($this->col_key && isset($row[$this->col_key])) {
                                $ck = $row[$this->col_key];
                                unset($row[$this->col_key]);
                                $result[$ck] = array_shift($row);
                            } else {
                                $result[] = array_shift($row);
                            }
                        }
                        $this->free();
                        return $result;
                        break;
                    case 'ONE':
                        $row = mysqli_fetch_row($this->sQuery);
                        $this->free();
                        return $row[0];
                        break;
                    default:
                        $result = array();
                        while ($row = mysqli_fetch_assoc($this->sQuery)) {
                            $result[] = $row;
                        }
                        $this->free();
                        return $result;
                        break;
                }
                break;
        }
    }

    /**
     * 执行SQL
     * @param $sql
     * @throws Exception
     */
    protected function query($sql)
    {
        $this->sQuery  = mysqli_query($this->link, $sql);
        $this->lastSql = $sql;
        if (mysqli_errno($this->link)) {
            throw new Exception(mysqli_error($this->link));
        }
    }

    protected function free()
    {
        mysqli_free_result($this->sQuery);
    }

    /**
     * 设置 flag.
     *
     * @param string $flag
     * @param bool $enable
     */
    protected function setFlag($flag, $enable = true)
    {
        if ($enable) {
            $this->flags[$flag] = true;
        } else {
            unset($this->flags[$flag]);
        }
    }

    /**
     * 生成完整 SQL 语句
     *
     * @return string
     * @throws Exception
     */
    protected function build()
    {
        switch ($this->type) {
            case 'DELETE':
                return $this->buildDELETE();
            case 'INSERT':
                return $this->buildINSERT();
            case 'UPDATE':
                return $this->buildUPDATE();
            case 'SELECT':
                return $this->buildSELECT();
        }
        throw new Exception("type empty");
    }

    /**
     * 生成 flag
     *
     * @return string
     */
    protected function buildFlags()
    {
        if (!$this->flags) {
            return '';
        }
        return ' ' . implode(' ', array_keys($this->flags));
    }

    /**
     * 创建 SELECT SQL
     *
     * @return string
     */
    protected function buildSELECT()
    {
        return 'SELECT'
            . $this->buildFlags()
            . $this->buildCols()
            . $this->buildTable()
            . $this->buildWhere()
            . $this->buildGroupBy()
            . $this->buildHaving()
            . $this->buildOrderBy()
            . $this->buildLimit()
            . $this->buildForUpdate();
    }

    /**
     * 生成更新的 SQL 语句
     */
    protected function buildUPDATE()
    {
        return 'UPDATE'
            . $this->buildFlags()
            . $this->buildTable()
            . $this->buildValuesForUpdate()
            . $this->buildWhere()
            . $this->buildOrderBy()
            . $this->buildLimit()
            . $this->buildReturning();
    }

    /**
     * 创建 DELETE SQL
     */
    protected function buildDELETE()
    {
        return 'DELETE'
            . $this->buildFlags()
            . $this->buildTable()
            . $this->buildWhere()
            . $this->buildOrderBy()
            . $this->buildLimit()
            . $this->buildReturning();
    }

    /**
     * 生成 INSERT 语句
     *
     * @return string
     */
    protected function buildINSERT()
    {
        return 'INSERT'
            . $this->buildFlags()
            . $this->buildInto()
            . $this->buildValuesForInsert()
            . $this->buildReturning();
    }

    /**
     * 为 INSERT 语句绑定值
     *
     * @return string
     */
    protected function buildValuesForInsert()
    {
        return ' (' . $this->indentCsv(array_keys($this->col_values)) . ') VALUES (' .
            $this->indentCsv(array_values($this->col_values)) . ')';
    }

    /**
     * 生成 INTO 语句
     *
     * @return string
     */
    protected function buildInto()
    {
        return " INTO " . $this->table;
    }

    /**
     * 生成 RETURNING 语句
     *
     * @return string
     */
    protected function buildReturning()
    {
        if (!$this->returning) {
            return '';
        }
        return ' RETURNING' . $this->indentCsv($this->returning);
    }

    /**
     * 生成 where 语句
     *
     * @return string
     */
    protected function buildWhere()
    {
        if (!$this->where) {
            return '';
        }
        return ' WHERE' . $this->indent($this->where);
    }

    /**
     * 构建分组
     * @return string
     */
    protected function buildGroupBy()
    {
        if (!$this->group_by) {
            return '';
        }
        return ' group by' . $this->indentCsv($this->group_by);
    }

    /**
     * 为更新语句绑定值
     *
     * @return string
     */
    protected function buildValuesForUpdate()
    {
        $values = array();
        foreach ($this->col_values as $col => $value) {
            $values[] = "{$col} = {$value}";
        }
        return ' SET' . $this->indentCsv($values);
    }

    /**
     * 构建having
     * @return string
     */
    protected function buildHaving()
    {
        if (!$this->having) {
            return '';
        }
        return ' having' . $this->indentCsv($this->having);
    }

    /**
     * 构建order by
     * @return string
     */
    protected function buildOrderBy()
    {
        if (!$this->order_by) {
            return '';
        }
        if ($this->order_asc) {
            return ' ORDER BY' . $this->indentCsv($this->order_by) . ' ASC';
        } else {
            return ' ORDER BY' . $this->indentCsv($this->order_by) . ' DESC';
        }
    }

    /**
     * 构建limit
     * @return string
     */
    protected function buildLimit()
    {
        $has_limit  = $this->type == 'DELETE' || $this->type == 'UPDATE';
        $has_offset = $this->type == 'SELECT' || $this->type == '';
        if ($has_offset && $this->limit) {
            $clause = " LIMIT {$this->limit}";
            if ($this->offset) {
                $clause .= " OFFSET {$this->offset}";
            }
            return $clause;
        } elseif ($has_limit && $this->limit) {
            return " LIMIT {$this->limit}";
        }
        return '';
    }

    /**
     * 生成 FOR UPDATE 语句
     *
     * @return string
     */
    protected function buildForUpdate()
    {
        if (!$this->for_update) {
            return '';
        }
        return ' FOR UPDATE';
    }

    /**
     * 构建where
     * @param $field
     * @param $condition
     * @param null $value
     * @return string
     */
    protected function joinWhere($field, $condition, $value)
    {
        $condition = strtolower($condition);
        if (is_array($value)) {
            $value = join(',', $value);
        } else {
            $value = $this->quoteNamesIn($value);
        }
        $field = $this->parseKey($field);
        switch ($condition) {
            case '=':
            case '!=':
            case '<>':
            case '>':
            case '>=':
            case '<':
            case '<=':
                $string = $field . $condition . $value;
                break;
            case 'in':
                $string = $field . ' in (' . $value . ')';
                break;
            case 'notin':
            case 'not in':
                $string = $field . ' not in (' . $value . ')';
                break;
            case 'like':
                $string = $field . ' like ' . $value;
                break;
            case 'notlike':
            case 'not like':
                $string = $field . ' not like ' . $value;
                break;
            case 'instr':
                $string = "instr({$field}," . $value . ')';
                break;
            default:
                $string = $field . $condition . $value;
                break;
        }
        return $string;
    }

    /**
     * 字段处理
     * @access protected
     * @param string $key
     * @return string
     */
    protected function parseKey($key)
    {
        $key = trim($key);
        if (!is_numeric($key) && !preg_match('/[,\'\"\*\(\)`.\s]/', $key)) {
            $key = '`' . $key . '`';
        }
        return $key;
    }

    /**
     * Quotes "table.col" 格式的字符串
     *
     * @param string $text
     * @return string|array
     */
    public function quoteNamesIn($text)
    {
        if ($text === '') {
            return "''";
        }
        if (preg_match("/^[0-9]{1,11}$/", $text)) {
            return $text;
        }
        if ($text[0] == '"' && $text[strlen($text) - 1] == '""') {
            return $text;
        } elseif ($text[0] == "'" && $text[strlen($text) - 1] == "'") {
            return $text;
        } else {
            $text = addslashes($text);
            return "'{$text}'";
        }
    }

    /**
     * 哪个表
     *
     * @return string
     */
    protected function buildTable()
    {
        return " from {$this->table}";
    }

    /**
     * 生成 SELECT 列语句
     *
     * @return string
     * @throws Exception
     */
    protected function buildCols()
    {
        if (!$this->cols) {
            return ' *';
        }
        foreach ($this->cols as $key => $col) {
            if(stripos('as', $col) === false) {
                $this->cols[$key] = $this->parseKey($col);
            }
        }
        return $this->indentCsv($this->cols);
    }

    /**
     * 返回空格分隔的字符串
     *
     * @param array $list
     * @return string
     */
    protected function indent(array $list)
    {
        return ' ' . implode(' ', $list);
    }

    /**
     * 返回逗号分隔的字符串
     *
     * @param array $list
     * @return string
     */
    protected function indentCsv(array $list)
    {
        return ' ' . implode(',', $list);
    }

    /**
     * 设置错误
     * @param $status
     * @param $msg
     * @return array|string
     */
    protected function setErr($status, $msg)
    {
        $content = array(
            'status' => $status,
            'msg'    => $msg,
        );
        if (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') {

        }
        $content = json_encode($content);
        return $content;
    }
}